/*
 *this file is part of nseer erp
 *Copyright (C)2006-2010 Nseer(Beijing) Technology co.LTD/http://www.nseer.com 
 *
 *This program is free software; you can redistribute it and/or
 *modify it under the terms of the GNU General Public License
 *as published by the Free Software Foundation; either
 *version 2 of the License, or (at your option) any later version.
 */
package include.excel_import;

import java.io.InputStream;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.Vector;
import java.util.HashMap;
import java.util.Set;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;

public class XlsInfo extends Path {

    private HashMap rowCount = new HashMap();
    private HashMap columnCount = new HashMap();
    private HashMap columnTypes = new HashMap();
    private HashMap columnsNames = new HashMap();
    private HashMap sheets = new HashMap();
    private Vector sheetsName = new Vector();
    private Vector getRowValues_result = new Vector();
    private Vector getColumnsName_result = new Vector();
    private HashMap getColumnType_types = new HashMap();
    private DecimalFormat intFormat =
            MyDataFormat.intDecimalFormat;
    private DecimalFormat doubleFormat =
            MyDataFormat.doubleDecimalFormat;
    private SimpleDateFormat yyyymmddFormat =
            MyDataFormat.yyyymmddDateFormat;
    private HSSFWorkbook wb = null;
    private HSSFDataFormat df = null;

    public XlsInfo(HSSFWorkbook wb) {
        this.wb = wb;
        df = wb.createDataFormat();
    }

    public XlsInfo(String fileName) {
        String filename = getPath() + "/conf/excel_import/" + fileName;
        try {
            InputStream input = new FileInputStream(filename);
            POIFSFileSystem fs = new POIFSFileSystem(input);
            wb = new HSSFWorkbook(fs);
            df = wb.createDataFormat();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public int getRowCount(String sheetName) {
        if (rowCount.containsKey(sheetName)) {
            return ((Integer) rowCount.get(sheetName)).intValue();
        }
        HSSFSheet sheet = getSheetForSheetName(sheetName);
        int rowssize = sheet.getPhysicalNumberOfRows();
        int size = 0;
        for (int i = 0; i < rowssize; i++) {
            HSSFRow row = sheet.getRow(i);
            if (row != null) {
                size++;
            }
            row = null;
        }
        Integer mysize = new Integer(size);
        rowCount.put(sheetName, mysize);
        sheet = null;
        mysize = null;
        return size;
    }

    public int getColumnCount(String sheetName) {
        if (columnCount.containsKey(sheetName)) {
            return ((Integer) columnCount.get(sheetName)).intValue();
        }
        HSSFSheet sheet = getSheetForSheetName(sheetName);
        HSSFRow row = sheet.getRow(0);
        int size = 0;
        int cellssize = row.getPhysicalNumberOfCells();
        for (int i = 0; i < cellssize; i++) {
            HSSFCell cell = row.getCell((short) i);
            if (cell != null) {
                size++;
                cell = null;
            }
        }
        Integer mysize = new Integer(size);
        columnCount.put(sheetName, mysize);
        sheet = null;
        row = null;
        mysize = null;
        return size;
    }

    public Vector getColumnsName(String sheetName) {
        if (columnsNames.containsKey(sheetName)) {
            return (Vector) columnsNames.get(sheetName);
        }
        HSSFSheet sheet = getSheetForSheetName(sheetName);
        getColumnsName_result.clear();
        HSSFRow row = sheet.getRow((short) 0);
        int cellssize = row.getPhysicalNumberOfCells();
        for (int i = 0; i < cellssize; i++) {
            HSSFCell cell = row.getCell((short) i);
            getColumnsName_result.addElement(cell.getStringCellValue());
            cell = null;
        }
        columnsNames.put(sheetName, getColumnsName_result);
        sheet = null;
        row = null;
        return getColumnsName_result;
    }

    public Vector getRowValues(int rowNum, Vector columnNames, String sheetName) {
        getRowValues_result.clear();
        HSSFSheet sheet = getSheetForSheetName(sheetName);
        HSSFRow row = sheet.getRow(rowNum);
        for (int i = 0; i < getColumnCount(sheetName); i++) {
            try {
                Vector allColumnNames = getColumnsName(sheetName);
                String aName = (String) allColumnNames.elementAt(i);
                if (columnNames.contains(aName)) {
                    HSSFCell cell = row.getCell((short) i);
                    String itemType = getColumnType(aName, sheetName);
                    dump(getRowValues_result, cell, itemType);
                    cell = null;
                    itemType = null;
                }
                allColumnNames = null;
                aName = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        sheet = null;
        row = null;
        return getRowValues_result;
    }

    public Vector getRowValues(int rowNum, String sheetName) {
        getRowValues_result.clear();
        HSSFSheet sheet = getSheetForSheetName(sheetName);
        HSSFRow row = sheet.getRow(rowNum);
        for (int i = 0; i < getColumnCount(sheetName); i++) {
            HSSFCell cell = row.getCell((short) i);
            String columnName =
                    (String) getColumnsName(sheetName).elementAt(i);
            String itemType = getColumnType(columnName, sheetName);
            dump(getRowValues_result, cell, itemType);
            cell = null;
            columnName = null;
            itemType = null;
        }
        sheet = null;
        row = null;
        return getRowValues_result;
    }

    public String getColumnType(String columnName,
            String sheetName) {
        if (columnTypes.containsKey(sheetName + "-" + columnName)) {
            return (String) columnTypes.get(sheetName + "-" + columnName);
        }
        HSSFSheet sheet = getSheetForSheetName(sheetName);
        int index = getColumnsName(sheetName).indexOf(columnName);
        getColumnType_types.clear();
        for (int i = 1; i < getRowCount(sheetName); i++) {
            HSSFRow row = sheet.getRow(i);
            HSSFCell cell = row.getCell((short) index);
            if (index == -1) {
                System.err.println("getColumnType: index==-1");
            }
            if (cell == null) {
                System.err.println("getColumnType:cell==null");
            }
            String celltype = getCellDataType(cell);
            if (!getColumnType_types.containsKey(celltype)) {
                getColumnType_types.put(celltype, new Integer(1));
            } else {
                getColumnType_types.put(celltype, new Integer(((Integer) getColumnType_types.get(celltype)).intValue() + 1));
            }
            row = null;
            cell = null;
            celltype = null;
        }
        Set set = getColumnType_types.keySet();
        Iterator it = set.iterator();
        Integer max = new Integer(0);
        String realtype = "BLANK";
        int flag = 0;
        while (it.hasNext()) {
            String key = (String) it.next();
            if (flag == 0) {
                max = (Integer) getColumnType_types.get(key);
                realtype = key;
                flag++;
            } else if (max.compareTo((Integer) getColumnType_types.get(key)) < 0) {
                max = (Integer) getColumnType_types.get(key);
                realtype = key;
            }
        }
        columnTypes.put(sheetName + "-" + columnName, realtype);
        sheet = null;
        set = null;
        it = null;
        max = null;
        return realtype;
    }

    private String getCellDataType(HSSFCell cell) {
        String type = "";
        HSSFCellStyle cellstyle = cell.getCellStyle();
        short datatype = cellstyle.getDataFormat();
        if (df == null) {
            System.err.println("df==null");
        }
        String dataFormatStr = df.getFormat(datatype);
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                if (dataFormatStr.indexOf("0_") == 0 ||
                        dataFormatStr.indexOf("0;") == 0 ||
                        dataFormatStr.indexOf("#,##0_") == 0 ||
                        dataFormatStr.indexOf("#,##0;") == 0 ||
                        dataFormatStr.equals("0")) {
                    type = "INT";
                } else if (dataFormatStr.equals("yyyy\\-mm\\-dd") ||
                        dataFormatStr.equals("yyyy-mm-dd") ||
                        dataFormatStr.equals("yyyy/mm/dd") ||
                        dataFormatStr.equals("m/d/yy") ||
                        dataFormatStr.equals("0x1f")) {
                    type = "DATE";
                } else if (dataFormatStr.indexOf("#,##0.") == 0 ||
                        dataFormatStr.indexOf("0.0") == 0) {
                    type = "DOUBLE";
                } else if (dataFormatStr.equals("General")) {
                    type = "INT1";
                } else {
                    type = "NUMBERIC";
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:
                if (dataFormatStr.equals("General")) {
                    type = "STRING";
                } else if (dataFormatStr.equals("@")) {
                    type = "STRING";
                } else if (dataFormatStr.indexOf("0_") == 0 ||
                        dataFormatStr.indexOf("0;") == 0 ||
                        dataFormatStr.indexOf("#,##0_") == 0 ||
                        dataFormatStr.indexOf("#,##0;") == 0 ||
                        dataFormatStr.equals("0")) {
                    type = "INT";
                } else if (dataFormatStr.indexOf("#,##0.") == 0 ||
                        dataFormatStr.indexOf("0.0") == 0) {
                    type = "DOUBLE";
                } else {
                    System.out.println(dataFormatStr);
                }
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                type = "BLANK";
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                type = "FORMULA";
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                type = "ERROR";
                break;
            default:
                type = "UNKNOWN";
                break;
        }
        cellstyle = null;
        dataFormatStr = null;
        return type;
    }

    private HSSFSheet getSheetForSheetName(String sheetName) {
        if (sheets.containsKey(sheetName)) {
            return (HSSFSheet) sheets.get(sheetName);
        }
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            HSSFSheet sheet = wb.getSheetAt(i);
            String tablename = wb.getSheetName(i);
            sheets.put(sheetName, sheet);
            if (tablename.equals(sheetName)) {
                tablename = null;
                return sheet;
            }
        }
        return null;
    }

    public Vector getSheetsName() {
        if (sheetsName.size() > 0) {
            return sheetsName;
        }
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            String sheetName = wb.getSheetName(i);
            sheetsName.addElement(sheetName);
        }
        return sheetsName;
    }

    public void setRowCount(HashMap argRowCount) {
        this.rowCount = argRowCount;
    }

    public void setColumnCount(HashMap argColumnCount) {
        this.columnCount = argColumnCount;
    }

    public HashMap getColumnTypes() {
        return this.columnTypes;
    }

    public void setColumnTypes(HashMap argColumnTypes) {
        this.columnTypes = argColumnTypes;
    }

    public HashMap getColumnsNames() {
        return this.columnsNames;
    }

    public void setColumnsNames(HashMap argColumnsNames) {
        this.columnsNames = argColumnsNames;
    }

    public HashMap getSheets() {
        return this.sheets;
    }

    public void setSheets(HashMap argSheets) {
        this.sheets = argSheets;
    }

    private void dump(Vector result, HSSFCell cell, String itemType) {
        String type = getCellDataType(cell);
        String num, date = null;
        if (type.equals("INT")) {
            num =
                    String.valueOf(intFormat.format(cell.getNumericCellValue()));
            result.addElement(num);
        } else if (type.equals("STRING")) {
            result.addElement(cell.getStringCellValue());
        } else if (type.equals("DATE")) {
            date =
                    String.valueOf(yyyymmddFormat.format(cell.getDateCellValue()));
            result.addElement(date);
        } else if (type.equals("DOUBLE")) {
            num =
                    String.valueOf(doubleFormat.format(cell.getNumericCellValue()));
            result.addElement(num);
        } else if (type.equals("INT1")) {
            num =
                    String.valueOf(intFormat.format(cell.getNumericCellValue()));
            result.addElement(num);
        } else if (type.equals("BLANK")) {
            if (itemType.equals("STRING")) {
                result.addElement(" ");
            } else if (itemType.equals("INT")) {
                result.addElement(new Integer(0));
            } else if (itemType.equals("DOUBLE")) {
                result.addElement(new Double(0.00));
            } else if (itemType.equals("DATE")) {
                result.addElement("0000-00-00");
            } else if (itemType.equals("UNKNOWN")) {
                result.addElement(" ");
            }
        }
        type = null;
        num = null;
        date = null;
    }
}
